package cn.kivensoft.xlsx.writer;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

public class Worksheet {
	public static final float DEFAULT_COLUMN_WIDTH = 10f;
	public static final float DEFAULT_ROW_HEIGHT = 15f;

	public enum CellDirection {
		ColumnToColum, RowToRow
	}

	public enum SheetProtectionValue {
		// sheet, // Is alway on 1 if protected
		objects, scenarios, formatCells, formatColumns, formatRows, 
		insertColumns, insertRows, insertHyperlinks, deleteColumns,
		deleteRows, selectLockedCells, sort, autoFilter, pivotTables,
		selectUnlockedCells
	}

	private Style activeStyle;
	private Workbook workbookReference;
	private int sheetID;
	private String sheetName;
	private boolean useSheetProtection;
	private String sheetProtectionPassword;
	private int currentRowNumber;
	private int currentColumnNumber;
	private float defaultRowHeight;
	private float defaultColumnWidth;
	private Cell.Range selectedCells;
	private Map<Integer, Column> columns = new HashMap<Integer, Column>();
	private Map<String, Cell> cells = new HashMap<String, Cell>();
	private Map<String, Cell.Range> mergedCells = new HashMap<String, Cell.Range>();
	private Cell.Range autoFilterRange;
	private CellDirection currentCellDirection = CellDirection.ColumnToColum;
	private List<SheetProtectionValue> sheetProtectionValues = new ArrayList<SheetProtectionValue>();
	private Map<Integer, Float> rowHeights = new HashMap<Integer, Float>();
	private Map<Integer, Boolean> hiddenRows = new HashMap<Integer, Boolean>();

	public Cell.Range getSelectedCells() {
		return selectedCells;
	}

	public void setSelectedCells(Cell.Range value) {
		selectedCells = value;
	}

	public Map<Integer, Column> getColumns() {
		return columns;
	}

	public Cell.Range getAutoFilterRange() {
		return autoFilterRange;
	}

	public void setAutoFilterRange(Cell.Range value) {
		autoFilterRange = value;
	}

	public CellDirection getCurrentCellDirection() {
		return currentCellDirection;
	}

	public void setCurrentCellDirection(CellDirection value) {
		currentCellDirection = value;
	}

	public List<SheetProtectionValue> getSheetProtectionValues() {
		return sheetProtectionValues;
	}

	public boolean getUseSheetProtection() {
		return useSheetProtection;
	}

	public void setUseSheetProtection(boolean value) {
		useSheetProtection = value;
	}

	public String getSheetProtectionPassword() {
		return sheetProtectionPassword;
	}

	public void setSheetProtectionPassword(String password) {
		if (password == null || password.isEmpty()) {
			sheetProtectionPassword = null;
			useSheetProtection = false;
		} else {
			sheetProtectionPassword = password;
			useSheetProtection = true;
		}
	}

	public String getSheetName() {
		return sheetName;
	}

	public void setSheetName(String value) throws Exception {
		if (value == null || value.isEmpty()) 
			throw new Exception("The sheet name must be between 1 and 31 characters");
		if (value.length() > 31)
			throw new Exception("The sheet name must be between 1 and 31 characters");
		if (Pattern.matches("[\\[\\]\\*\\?/\\\\]", value))
			throw new Exception("The sheet name must not contain the characters [  ]  * ? / \\ ");

		sheetName = value;
	}

	public int getSheetID() {
		return sheetID;
	}

	public void setSheetID(int value) {
		sheetID = value;
	}

	public Map<String, Cell> getCells() {
		return cells;
	}

	public Map<String, Cell.Range> getMergedCells() {
		return mergedCells;
	}

	public float getDefaultRowHeight() {
		return defaultRowHeight;
	}

	public void setDefaultRowHeight(float value) {
		defaultRowHeight = value;
	}

	public float getDefaultColumnWidth() {
		return defaultColumnWidth;
	}

	public void setDefaultColumnWidth(float value) {
		defaultColumnWidth = value;
	}

	public Map<Integer, Float> getRowHeights() {
		return rowHeights;
	}

	public Map<Integer, Boolean> getHiddenRows() {
		return hiddenRows;
	}

	public Worksheet() {
		currentCellDirection = CellDirection.ColumnToColum;
		currentRowNumber = 0;
		currentColumnNumber = 0;
		defaultColumnWidth = DEFAULT_COLUMN_WIDTH;
		defaultRowHeight = DEFAULT_ROW_HEIGHT;
		activeStyle = null;
		workbookReference = null;
	}

	public Worksheet(String name, int id) throws Exception {
		this();
		setSheetName(name);
		sheetID = id;
	}

	public void addNextCellFormula(String formula) throws Exception {
		Cell c = new Cell(formula, Cell.CellType.FORMULA, currentColumnNumber,
				currentRowNumber);
		addNextCell(c, true);
	}

	public void addNextCell(Object value) throws Exception {
		Cell c = new Cell(value, Cell.CellType.DEFAULT, currentColumnNumber,
				currentRowNumber);
		addNextCell(c, true);
	}

	private void addNextCell(Cell cell, boolean incremental) throws Exception {
		if (activeStyle != null) cell.setStyle(activeStyle, workbookReference);

		String address = cell.getCellAddress();
		cells.put(address, cell);
		if (incremental) {
			if (currentCellDirection == CellDirection.ColumnToColum)
				currentColumnNumber++;
			else currentRowNumber++;
			
		} else {
			if (currentCellDirection == CellDirection.ColumnToColum) {
				currentColumnNumber = cell.columnAddress + 1;
				currentRowNumber = cell.rowAddress;
			} else {
				currentColumnNumber = cell.columnAddress;
				currentRowNumber = cell.rowAddress + 1;
			}
		}
	}

	public void addCell(Object value, int columnAddress, int rowAddress) throws Exception {
		Cell c = new Cell(value, Cell.CellType.DEFAULT, columnAddress, rowAddress);
		addNextCell(c, false);
	}

	public void addCell(Object value, String address) throws Exception {
		int column = Cell.resolveColumn(address);
		int row = Cell.resolveRow(address);
		addCell(value, column, row);
	}

	public void addCell(Cell cell) throws Exception {
		addNextCell(cell, false);
	}

	public void addCellFormula(String formula, String address) throws Exception {
		int column = Cell.resolveColumn(address);
		int row = Cell.resolveRow(address);
		Cell c = new Cell(formula, Cell.CellType.FORMULA, column, row);
		addNextCell(c, false);
	}

	public void addCellFormula(String formula, int columnAddress, int rowAddress) throws Exception {
		Cell c = new Cell(formula, Cell.CellType.FORMULA, columnAddress, rowAddress);
		addNextCell(c, false);
	}

	public void addCellRange(List<Object> values, Cell.Address startAddress,
			Cell.Address endAddress) throws Exception {
		addCellRangeInternal(values, startAddress, endAddress);
	}

	public void addCellRange(List<Object> values, String cellRange) throws Exception {
		Cell.Range range = Cell.resolveCellRange(cellRange);
		addCellRangeInternal(values, range.startAddress, range.endAddress);
	}

	private <T> void addCellRangeInternal(List<T> values,
			Cell.Address startAddress, Cell.Address endAddress) throws Exception {
		List<Cell.Address> addresses = Cell.getCellRange(startAddress, endAddress);
		if (values.size() != addresses.size())
			throw new Exception("The number of passed values (" + values.size()
					+ ") differs from the number of cells within the range ("
					+ addresses.size() + ")");

		List<Cell> list = Cell.convertArray(values);
		for (int i = 0, len = values.size(); i < len; i++) {
			Cell c = list.get(i);
			Cell.Address a = addresses.get(i);
			c.rowAddress = a.rowIndex;
			c.columnAddress = a.columnIndex;
			addNextCell(c, false);
		}
	}

	public boolean removeCell(int columnAddress, int rowAddress) throws Exception {
		String address = Cell.resolveCellAddress(columnAddress, rowAddress);
		Cell c = cells.remove(address);
		return c != null;
	}

	public boolean removeCell(String address) throws Exception {
		int column = Cell.resolveColumn(address);
		int row = Cell.resolveRow(address);
		return removeCell(column, row);
	}

	public void goToNextColumn() {
		currentColumnNumber++;
		currentRowNumber = 0;
	}

	public void goToNextRow() {
		currentRowNumber++;
		currentColumnNumber = 0;
	}

	public void setCurrentRowAddress(int rowAddress) throws Exception {
		if (rowAddress >= 1048576 || rowAddress < 0)
			throw new Exception( "The row number (" + rowAddress
				+ ") is out of range. Range is from 0 to 1048575 (1048576 rows).");

		currentRowNumber = rowAddress;
	}

	public void setCurrentColumnAddress(int columnAddress) throws Exception {
		if (columnAddress >= 16383 || columnAddress < 0)
			throw new Exception("The column number (" + columnAddress
				+ ") is out of range. Range is from 0 to 16383 (16384 columns).");

		currentColumnNumber = columnAddress;
	}

	public void setCurentCellAddress(String address) throws Exception {
		int column = Cell.resolveColumn(address);
		int row = Cell.resolveRow(address);
		setCurentCellAddress(column, row);
	}

	public void setCurentCellAddress(int columnAddress, int rowAddress) throws Exception {
		setCurrentColumnAddress(columnAddress);
		setCurrentRowAddress(rowAddress);
	}

	public void setColumnWidth(String columnAddress, float width) throws Exception {
		int columnNumber = Cell.resolveColumn(columnAddress);
		setColumnWidth(columnNumber, width);
	}

	public void setColumnWidth(int columnNumber, float width) throws Exception {
		if (columnNumber >= 16384 || columnNumber < 0)
			throw new Exception("The column number (" + columnNumber
				+ ") is out of range. Range is from 0 to 16383 (16384 columns).");

		if (width < 0 || width > 255)
			throw new Exception("The column width (" + width
				+ ") is out of range. Range is from 0 to 255 (chars).");

		Column c = columns.get(columnNumber);
		if (c == null) { 
			c = new Column(columnNumber);
			columns.put(columnNumber, c);
		}
		c.width = width;
	}

	public void setRowHeight(int rowNumber, float height) throws Exception {
		if (rowNumber >= 1048576 || rowNumber < 0)
			throw new Exception("The row number (" + rowNumber
				+ ") is out of range. Range is from 0 to 1048575 (1048576 rows).");

		if (height < 0 || height > 409.5)
			throw new Exception("The row height (" + height
				+ ") is out of range. Range is from 0 to 409.5 (equals 546px).");

		rowHeights.put(rowNumber, height);
	}

	public void setActiveStyle(Style style, Workbook workbookReference) {
		activeStyle = style;
		this.workbookReference = workbookReference;
	}

	public void clearActiveStyle() {
		activeStyle = null;
		workbookReference = null;
	}

	public String mergeCells(Cell.Range cellRange) {
		return mergeCells(cellRange.startAddress, cellRange.endAddress);
	}

	public String mergeCells(String cellRange) throws Exception {
		Cell.Range range = Cell.resolveCellRange(cellRange);
		return mergeCells(range.startAddress, range.endAddress);
	}

	public String mergeCells(Cell.Address startAddress, Cell.Address endAddress) {
		String key = startAddress.toString() + ":" + endAddress.toString();
		Cell.Range value = new Cell.Range(startAddress, endAddress);
		if (!mergedCells.containsKey(key)) mergedCells.put(key, value);
		return key;
	}

	public void removeMergedCells(String range) throws Exception {
		range = range.toUpperCase();
		if (!mergedCells.containsKey(range)) {
			throw new Exception("The cell range " + range
				+ " was not found in the list of merged cell ranges");
		} else {
			List<Cell.Address> addresses = Cell.getCellRange(range);
			for (Cell.Address address : addresses) {
				if (cells.containsKey(addresses.toString())) {
					Cell cell = cells.get(address.toString());
					cell.fieldtype = Cell.CellType.DEFAULT;
					// resets the type
					if (cell.value == null) cell.value = "";
				}
			}
			mergedCells.remove(range);
		}
	}

	public void addAllowedActionOnSheetProtection(SheetProtectionValue typeOfProtection) {
		if (!sheetProtectionValues.contains(typeOfProtection)) {
			if (typeOfProtection == SheetProtectionValue.selectLockedCells
					&& !sheetProtectionValues.contains(SheetProtectionValue.selectUnlockedCells)) {
				sheetProtectionValues.add(SheetProtectionValue.selectUnlockedCells);
			}
			sheetProtectionValues.add(typeOfProtection);
			setUseSheetProtection(true);
		}
	}

	public void addHiddenRow(int rowNumber) throws Exception {
		setRowHiddenState(rowNumber, true);
	}

	public void removeHiddenRow(int rowNumber) throws Exception {
		setRowHiddenState(rowNumber, false);
	}

	private void setRowHiddenState(int rowNumber, boolean state) throws Exception {
		if (rowNumber >= 1048576 || rowNumber < 0)
			throw new Exception("The row number (" + rowNumber
				+ ") is out of range. Range is from 0 to 1048575 (1048576 rows).");

		if (hiddenRows.containsKey(rowNumber)) {
			if (state) hiddenRows.put(rowNumber, state);
			else hiddenRows.remove(rowNumber);
		} else if (state) hiddenRows.put(rowNumber, state);
	}

	public void addHiddenColumn(int columnNumber) throws Exception {
		setColumnHiddenState(columnNumber, true);
	}

	public void addHiddenColumn(String columnAddress) throws Exception {
		int columnNumber = Cell.resolveColumn(columnAddress);
		setColumnHiddenState(columnNumber, true);
	}

	public void removeHiddenColumn(int columnNumber) throws Exception {
		setColumnHiddenState(columnNumber, false);
	}

	public void removeHiddenColumn(String columnAddress) throws Exception {
		int columnNumber = Cell.resolveColumn(columnAddress);
		setColumnHiddenState(columnNumber, false);
	}

	private void setColumnHiddenState(int columnNumber, boolean state) throws Exception {
		if (columnNumber >= 16384 || columnNumber < 0)
			throw new Exception("The column number (" + columnNumber
				+ ") is out of range. Range is from 0 to 16383 (16384 columns).");

		if (columns.containsKey(columnNumber) && state) {
			columns.get(columnNumber).isHidden = state;
		} else if (state) {
			Column c = new Column(columnNumber);
			c.setIsHidden(state);
			columns.put(columnNumber, c);
		}
	}

	public void setAutoFilter(int startColumn, int endColumn) throws Exception{
		if (startColumn >= 16384 || startColumn < 0)
			throw new Exception("The start column number (" + startColumn
				+ ") is out of range. Range is from 0 to 16383 (16384 columns).");

		if (endColumn >= 16384 || endColumn < 0)
			throw new Exception("The end column number (" + endColumn
				+ ") is out of range. Range is from 0 to 16383 (16384 columns).");

		String start = Cell.resolveCellAddress(startColumn, 0);
		String end = Cell.resolveCellAddress(endColumn, 0);
		if (endColumn < startColumn) setAutoFilter(end + ":" + start);
		else setAutoFilter(start + ":" + end);
	}

	public void setAutoFilter(String range) throws Exception {
		setAutoFilterRange(Cell.resolveCellRange(range));
		recalculateAutoFilter();
		recalculateColumns();
	}

	public void removeAutoFilter() {
		setAutoFilterRange(null);
	}

	public void recalculateAutoFilter() {
		if (getAutoFilterRange() == null) return;

		int start = autoFilterRange.startAddress.columnIndex;
		int end = getAutoFilterRange().endAddress.columnIndex;
		int endRow = 0;
		for (Map.Entry<String, Cell> item : cells.entrySet()) {
			int colAddr = item.getValue().columnAddress;
			int rowAddr = item.getValue().rowAddress;
			if (colAddr < start || colAddr > end) continue;
			if (rowAddr > endRow) endRow = rowAddr;
		}
		for (int i = start; i <= end; i++) {
			Column c = columns.get(i);
			if (c == null) {
				c = new Column(i);
				columns.put(i, c);
			}
			c.hasAutoFilter = true;
		}
		Cell.Range temp = new Cell.Range(new Cell.Address(start, 0),
				new Cell.Address(end, endRow));
		autoFilterRange = temp;
	}

	public void recalculateColumns() {
		List<Integer> columnsToDelete = new ArrayList<Integer>();
		for (Map.Entry<Integer, Column> item : columns.entrySet()) {
			Column c = item.getValue();
			if (c.hasAutoFilter || c.isHidden || c.width == DEFAULT_COLUMN_WIDTH)
				columnsToDelete.add(item.getKey());
		}
		for (Integer index : columnsToDelete) columns.remove(index);
	}

	public void setSelectedCells(Cell.Address startAddress, Cell.Address endAddress) {
		setSelectedCells(new Cell.Range(startAddress, endAddress));
	}

	public void setSelectedCells(String range) throws Exception {
		setSelectedCells(Cell.resolveCellRange(range));
	}

	public void removeSelectedCells() {
		selectedCells = null;
	}

	public static class Column {
		private int number;
		private String columnAddress = new String();
		private float width;
		private boolean isHidden;
		private boolean hasAutoFilter;

		public int getNumber() {
			return number;
		}

		public void setNumber(int value) {
			columnAddress = Cell.resolveColumnAddress(value);
			number = value;
		}

		public String getColumnAddress() {
			return columnAddress;
		}

		public void setColumnAddress(String value) {
			number = Cell.resolveColumn(value);
			columnAddress = value;
		}

		public float getWidth() {
			return width;
		}

		public void setWidth(float value) {
			width = value;
		}

		public boolean getIsHidden() {
			return isHidden;
		}

		public void setIsHidden(boolean value) {
			isHidden = value;
		}

		public boolean getHasAutoFilter() {
			return hasAutoFilter;
		}

		public void setHasAutoFilter(boolean value) {
			hasAutoFilter = value;
		}

		public Column() {
			setWidth(DEFAULT_COLUMN_WIDTH);
		}

		public Column(int columnCoordinate) {
			this();
			setNumber(columnCoordinate);
		}

		public Column(String columnAddress) {
			this();
			setColumnAddress(columnAddress);
		}

	}

}
